package com.dhcc.DRGsHosp.application.impl;

import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.inject.Named;

import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.dayatang.querychannel.QueryChannelService;
import org.openkoala.koalacommons.resourceloader.util.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.MonitorResultApplication;
import com.dhcc.DRGsHosp.core.domain.IndexCal;
import com.dhcc.DRGsHosp.infra.util.DrgsIndex;
/**
 * 
 * @ClassName: MonitorResultApplicationImpl  
 * @Description ：    
 * @Date: 2017年7月22日 上午11:26:01  
 * @Author LiuHongJun 
 * @Version 2.5.0 DHC-DRGs
 */
@Named
@Transactional
public class MonitorResultApplicationImpl implements MonitorResultApplication {

	  @Autowired  
	  private JdbcTemplateObj jdbcTemplateObj; 
	  
	private QueryChannelService queryChannel;
	
    private QueryChannelService getQueryChannelService(){
       if(queryChannel==null){
          queryChannel = InstanceFactory.getInstance(QueryChannelService.class,"queryChannel");
       }
     return queryChannel;
    }
    
    private EntityRepository repository;

	private EntityRepository getRepository() {
		if (repository == null) {
			repository = InstanceFactory.getInstance(
					EntityRepository.class, "repository");
		}
		return repository;
	}	
    
	public IndexCal getIndexCal(String mrId) {
		return IndexCal.get(IndexCal.class, mrId);
	}
	
	public void creatIndexCal(IndexCal indexCal) {
		indexCal.save();
	}
	
	public void updateIndexCal(IndexCal indexCal) {
		indexCal .save();
	}
	
	public void removeIndexCal(IndexCal indexCal) {
		if(indexCal != null){
			indexCal.remove();
		}
	}
	
	public void removeIndexCals(Set<IndexCal> IndexCals) {
		for (IndexCal indexCal : IndexCals) {
			indexCal.remove();
		}
	}
	
	public List<IndexCal> findAllIndexCal() {
		return IndexCal.findAll(IndexCal.class);
	}
	
	
	/**
	 * 
	 * @methodname :testIndexCalQuery
	 * @description :监测库查询数据
	 * @param :
	 * @author :liqingyang
	 * @return :
	 * @date :2015年4月3日
	 */
	@SuppressWarnings("unchecked")
	public List<IndexCal> findListByparam(IndexCal indexCal){
	
		String pType = "0";//病人类型
		if(StringUtils.hasLength(indexCal.getpType())){
			pType = indexCal.getpType();
		}
		
	   	StringBuilder jpql = new StringBuilder("select _IndexCal from IndexCal _IndexCal   where 1=1 ");
	   	if (indexCal.getPardepCode() != null && !"".equals(indexCal.getPardepCode())) {
	   		jpql.append(" and _IndexCal.pardepCode = '"+indexCal.getPardepCode()+"'");
	   	}
	   	else{
	   		jpql.append(" and _IndexCal.pardepCode = 'DRG_DHCC'");
	   	}
	   		
	   	if (indexCal.getPardrgCode() != null && !"".equals(indexCal.getPardrgCode())) {
	   		jpql.append(" and _IndexCal.pardrgCode = '"+indexCal.getPardrgCode()+"'");
	   	}
	   	else{
	   		jpql.append(" and _IndexCal.pardrgCode = 'DRG_DHCC'");
	   	}
	   	if (indexCal.getPardepgroupCode() != null && !"".equals(indexCal.getPardepgroupCode())) {
	   		jpql.append(" and _IndexCal.pardepgroupCode = '"+indexCal.getPardepgroupCode()+"'");
	   	}
	   	else{
	   		jpql.append(" and _IndexCal.pardepgroupCode = 'DRG_DHCC'");
	   	}
	   	if (indexCal.getPargroupName() != null && !"".equals(indexCal.getPargroupName())) {
	   		jpql.append(" and _IndexCal.pargroupName = '"+indexCal.getPargroupName()+"'");
	   	}
	   	else{
	   		jpql.append(" and _IndexCal.pargroupName = 'DRG_DHCC'");
	   	}
	   	if (indexCal.getParmdcCode() != null && !"".equals(indexCal.getParmdcCode())) {
	   		jpql.append(" and _IndexCal.parmdcCode = '"+indexCal.getParmdcCode()+"'");
	   	}
	   	else{
	   		jpql.append(" and _IndexCal.parmdcCode = 'DRG_DHCC'");
	   	}
	   	if (indexCal.getParadrgCode() != null && !"".equals(indexCal.getParadrgCode())) {
	   		jpql.append(" and _IndexCal.paradrgCode = '"+indexCal.getParadrgCode()+"'");
	   	}
	   	else{
	   		jpql.append(" and _IndexCal.paradrgCode = 'DRG_DHCC'");
	   	}
	   	if (indexCal.getParwardCode() != null && !"".equals(indexCal.getParwardCode())) {
	   		jpql.append(" and _IndexCal.parwardCode = '"+indexCal.getParwardCode()+"'");
	   	}
		else{
	   		jpql.append(" and _IndexCal.parwardCode = 'DRG_DHCC'");
	   	}
		if (indexCal.getProtomedicus() != null && !"".equals(indexCal.getProtomedicus())) {
	   		jpql.append(" and _IndexCal.protomedicus = '"+indexCal.getProtomedicus()+"'");
	   	}
		else{
	   		jpql.append(" and _IndexCal.protomedicus = 'DRG_DHCC'");
	   	}
		if (StringUtils.hasLength(indexCal.getResident())) {
			jpql.append(" and _IndexCal.resident = '"+indexCal.getResident()+"'");
		}else{
	   		jpql.append(" and _IndexCal.resident = 'DRG_DHCC'");
		}
		// 注释报错
//	   	if (indexCal.getMrLot() != null && !"".equals(indexCal.getMrLot())) {
//	   		jpql.append(" and _IndexCal.mrLot = '"+indexCal.getMrLot()+"'");
//	   	}		
//	   	if (indexCal.getMrStdtype() != null && !"".equals(indexCal.getMrStdtype())) {
////	   		if("month".equals(indexCal.getMrStdtype()) || "period".equals(indexCal.getMrStdtype())){
////	   			jpql.append(" and _IndexCal.mrStdtype = 'period'");
////	   		}else{
//	   			jpql.append(" and _IndexCal.mrStdtype = 'year'");
////	   		}
//	   	}	
	   	jpql.append(" and _IndexCal.pType='"+pType+"' ");
	   	
	   	List<IndexCal> mrList = getQueryChannelService()
		   .createJpqlQuery(jpql.toString())
		   .list();
	   	
	   	return mrList;
	}

	/**
	 * 
	 * @methodname    :getQuatrantChart
	 * @description   : 科室指标象限分析
	 * @param         :
	 * @author        :fulianxia
	 * @return        :
	 * @date          :2015年5月13日
	 */
	@Override
	public List<Object[]> getQuatrantChart(IndexCal indexCal) {/*
//		String mrLot = indexCal.getMrLot();
		StringBuilder sql = new StringBuilder();
		sql.append("select dep.dep_name,dtl.add_quota_code,"
		          + " case when dtl.add_quota_code='AvgFee' then round(dtl.add_value - age.manage_benchmark,2)  "
		          + " else round(dtl.add_value - age.manage_benchmark,2) end as value  "
		          + " from (select d.dep_code,d.dep_name,mr.* from DRGs.t_archivedata mr "
		          + " left join DRGs.t_department d  on mr.ad_dep_code = d.dep_code "
		          + " where  mr.ad_depgroup_code ='DRG_DHCC' "
		          + " and mr.ad_mdc_code ='DRG_DHCC' "
		          + " and mr.ad_group_name ='DRG_DHCC' "
		          + " and mr.ad_doctor ='DRG_DHCC' "
		          + " and mr.ad_drg_code ='DRG_DHCC' "
		          + " and mr.ad_adrg_code ='DRG_DHCC' "
		          + " and mr.ad_dep_code <> 'DRG_DHCC' "
		          + " ) dep "
		          + " left join DRGs.t_archivedatadtl dtl on dtl.add_id=dep.ad_id "
		          + " left join DRGs.t_quotamanage age on age.pardep_code=dep.dep_code "
		       // 注释报错
//		          + " where dep.ad_lot='"+mrLot+"' and dep.ad_std=substr('"+mrLot+"',1,4) "
//		          + " and (dtl.add_quota_code='AvgFee' or dtl.add_quota_code='DrugRatio' or dtl.add_quota_code='AvgDays') and dtl.add_value <> 0 "
//		          + " and dtl.add_quota_code = age.parquota_code "
//		          + " and age.manage_year=substr('"+mrLot+"',1,4) and age.pardep_code is not null "
		          + " and age.pardrg_code is null  and age.pardepgroup_code is null ");
		List<Object[]> list = getQueryChannelService().createSqlQuery(sql.toString()).list();
		if(list.size() <= 0){
			sql = new StringBuilder();
			sql.append("  select dep.dep_name,dtl.parquota_code,  "
			          + " case when dtl.parquota_code='AvgFee' then round(dtl.quota_detail - age.manage_benchmark,2)  "
			          + " else round(dtl.quota_detail - age.manage_benchmark,2) end as value   "
			          + " from (select d.dep_code,d.dep_name,mr.* from DRGs.t_IndexCal mr "
			          + " left join DRGs.t_department d  on mr.pardep_code = d.dep_code "
			          + " where  mr.pardepgroup_code ='DRG_DHCC' "
			          + " and mr.parmdc_code ='DRG_DHCC' "
			          + " and mr.pargroup_name ='DRG_DHCC' "
			          + " and mr.protomedicus ='DRG_DHCC' "
			          + " and mr.pardrg_code ='DRG_DHCC' "
			          + " and mr.paradrg_code ='DRG_DHCC' "
			          + " and mr.pardep_code <> 'DRG_DHCC' "
			          + " and mr.type=0 "
			          + " ) dep "
			          + " left join DRGs.t_quotadtl dtl on dep.mr_id=dtl.parmr_id "
			          + " left join DRGs.t_quotamanage age on age.pardep_code=dep.dep_code "
			       // 注释报错
//			          + " where dep.mr_lot='"+mrLot+"' and dep.mr_std_type='year' "
//			          + "  and (dtl.parquota_code='AvgFee' or dtl.parquota_code='DrugRatio' or dtl.parquota_code='AvgDays') and dtl.quota_detail <> 0 "
//			          + "  and dtl.parquota_code = age.parquota_code  "
//			          + "  and age.manage_year=substr('"+mrLot+"',1,4) and age.pardep_code is not null  "
			          + "  and age.pardrg_code is null   and age.pardepgroup_code is null "
			          + " order by dep.dep_name ");
			          
			list = getQueryChannelService().createSqlQuery(sql.toString()).list();
		}
		return list;
	*/
		return null;
	}
	

	
	/**
	 * 
	 * @methodname    :getQuotaAnalysisDetail
	 * @description   : 获取重点指标综合分析详情数据
	 * @param         :
	 * @author        :fulianxia
	 * @return        :
	 * @date          :2015年5月25日
	 */
	@Override
	public Map<String,Object> getQuotaAnalysisDetail(IndexCal indexCal,String quotaCodec){

		List<Map<String,Object>> listMap = new LinkedList<Map<String,Object>>();
		Map<String,Object> map = new HashMap<String,Object>();
		// 注释报错
//		String mrLot = indexCal.getMrLot();
		String mrType = indexCal.getMrType();
//		String year = mrLot.substring(0, 4);
		String depGroupCode = indexCal.getPardepgroupCode();
		String depCode = indexCal.getPardepCode();
		String clinicName = indexCal.getPargroupName();
		
		StringBuilder param = new StringBuilder();
		StringBuilder timeparam = new StringBuilder();
		if(StringUtils.hasLength(depGroupCode)&&!StringUtils.hasLength(depCode)){
			param.append(" and depgroup_code = '"+depGroupCode+"' ");
		}else if(StringUtils.hasLength(depCode)&&!StringUtils.hasLength(clinicName)){
			param.append(" and dep_code = '"+depCode+"' ");
		}else if(StringUtils.hasLength(depCode)&&StringUtils.hasLength(clinicName)){
			param.append(" and dep_code = '"+depCode+"' ");
			param.append(" and pargroup_name = '"+clinicName+"' ");
		}
		// 注释报错
//		if("M".equals(mrType)){
//			String mrM = mrLot;
//			if(mrLot.length()==8){
//				mrM = mrM.replace("Y", "");
//				mrM = mrM.replace("M", "");
//			}else{
//				mrM = mrM.replace("Y", "0");
//				mrM = mrM.replace("M", "");
//			}
//			
//			if("MYSQL".equals(ConstantEnum.DBTYPE)){
//				timeparam.append(" and date_format(out_date, '%Y%m')='"+mrM+"' ");
//			}else{
//				timeparam.append(" and to_char(out_date, 'yyyyMM')='"+mrM+"' ");
//			}
//		}else if("Q".equals(mrType)){
//			String mrQ = mrLot;
//			mrQ = mrQ.replace("Y", "");
//			mrQ = mrQ.replace("Q", "");
//			
//			if("MYSQL".equals(ConstantEnum.DBTYPE)){
//				timeparam.append(" and date_format(out_date, '%YQ')='"+mrQ+"' ");
//			}else{
//				timeparam.append(" and to_char(out_date, 'yyyyQ')='"+mrQ+"' ");
//			}
//			
//		}else if("MC".equals(mrType)){
//			String mrMC = mrLot;
//			if(mrLot.length()==9){
//				mrMC = mrMC.replace("Y", "");
//				mrMC = mrMC.replace("MC", "");
//			}else{
//				mrMC = mrMC.replace("Y", "0");
//				mrMC = mrMC.replace("MC", "");
//			}
//
//			if("MYSQL".equals(ConstantEnum.DBTYPE)){
//				timeparam.append(" and date_format(out_date, '%Y-%m')>='"+year+"01' ");
//				timeparam.append(" and date_format(out_date, '%Y-%m')<='"+mrMC+"' ");
//			}else{
//				timeparam.append(" and to_char(out_date, 'yyyyMM')>='"+year+"01' ");
//				timeparam.append(" and to_char(out_date, 'yyyyMM')<='"+mrMC+"' ");
//			}
//		}else if("Y".equals(mrType)){
//			
//			if("MYSQL".equals(ConstantEnum.DBTYPE)){
//				timeparam.append(" and date_format(out_date, '%Y')='"+year+"' ");
//			}else{
//				timeparam.append(" and to_char(out_date, 'yyyy')='"+year+"' ");
//			}
//		}
//		
		//中位数、最大值、最小值、标准差
		String sql = " select median("+quotaCodec+") mid_Value, max("+quotaCodec+") max_value, min("+quotaCodec+") min_value, stddev("+quotaCodec+") devia_Value from DRGs.t_medicalrecord a ";
		sql += " left join DRGs.t_department f on a.out_dep=f.dep_name";
		sql += " left join DRGs.t_depgroup g on f.pardepgroup_code=g.depgroup_code";
		sql += " where 1=1 ";
		sql += param.toString();
		sql += timeparam.toString();
		List<Object[]> objects = getQueryChannelService().createSqlQuery(sql).list();
		Object[] obj = objects.get(0);
		map.put("midValue", obj[0]);
		map.put("maxValue", obj[1]);
		map.put("minValue", obj[2]);
		map.put("deviaValue", obj[3]);
		
		//获取上四分位数
		Double midValue = Double.parseDouble(obj[0].toString());
		String sql1 = " select median("+quotaCodec+") mid_Value from DRGs.t_medicalrecord a ";
		sql1 += " left join DRGs.t_department f on a.out_dep=f.dep_name";
		sql1 += " left join DRGs.t_depgroup g on f.pardepgroup_code=g.depgroup_code";
		sql1 += " where 1=1 ";
		sql1 += param.toString();
		sql1 += timeparam.toString();
		sql1 += " and "+quotaCodec+"<"+midValue+" ";
		List<Object> quarterValues = getQueryChannelService().createSqlQuery(sql1).list();
		Object value = quarterValues.get(0);
		Double quarterValue = 0d;
		if(value != null && !"".equals(value)){
			quarterValue = Double.parseDouble(value.toString());
		}
		map.put("quarterValue", quarterValue.toString());

		//获取下四分位数
		sql1 = " select median("+quotaCodec+") mid_Value from DRGs.t_medicalrecord a ";
		sql1 += " left join DRGs.t_department f on a.out_dep=f.dep_name";
		sql1 += " left join DRGs.t_depgroup g on f.pardepgroup_code=g.depgroup_code";
		sql1 += " where 1=1 ";
		sql1 += param.toString();
		sql1 += timeparam.toString();
		sql1 += " and "+quotaCodec+">"+midValue+" ";
		
		List<Object> quarter3Values = getQueryChannelService().createSqlQuery(sql1).list();
		Object value3 = quarter3Values.get(0);
		Double quarter3Value = 0d;
		if(value3 != null && !"".equals(value3)){
			quarter3Value = Double.parseDouble(value3.toString());
		}
		map.put("quarter3Value", quarter3Value.toString());

		return map;
	}
	

	@Override
	public int queryMinYear() {
		String sql = "select min(mr_dateto) from DRGs.t_IndexCal t where t.PARDEPGROUP_CODE='DRG_DHCC' and t.PARDEP_CODE ='DRG_DHCC'"+
				" and t.PARGROUP_NAME= 'DRG_DHCC' and t.PARMDC_CODE='DRG_DHCC' and t.PARADRG_CODE = 'DRG_DHCC' and t.PARDRG_CODE = 'DRG_DHCC' and t.PROTOMEDICUS ='DRG_DHCC' and t.MR_TYPE in('Y','MC')";
		List<Object> tl = getQueryChannelService().createSqlQuery(sql).list();
		int min=9999;
		if(!tl.isEmpty()&&null!=tl.get(0))
		{
			min=Integer.parseInt(tl.get(0).toString().substring(0, 4));
		}
		return min;
	}

	@Override
	public int queryMaxYear() {
		String sql = "select max(mr_dateto) from DRGs.t_IndexCal t where t.PARDEPGROUP_CODE='DRG_DHCC' and t.PARDEP_CODE ='DRG_DHCC'"+
				" and t.PARGROUP_NAME= 'DRG_DHCC' and t.PARMDC_CODE='DRG_DHCC' and t.PARADRG_CODE = 'DRG_DHCC' and t.PARDRG_CODE = 'DRG_DHCC' and t.PROTOMEDICUS ='DRG_DHCC' and t.MR_TYPE ='MC'";
		List<Object> tl = getQueryChannelService().createSqlQuery(sql).list();
		int max=0;
		if(!tl.isEmpty()&&null!=tl.get(0))
		{
			max=Integer.parseInt(tl.get(0).toString().substring(0, 4));
		}
		return max;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.IndexCalApplication#findAllMonthData(com.dhcc.DRGsHosp.core.domain.IndexCal)
	 */
	@Override
	public List<Object[]> findAllMonthData(IndexCal indexCal,String quotaCode) {
		
		String codeStr = "";
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT TO_CHAR (M .MR_DATEFROM, 'YYYY-MM') AS \"month\","
				+ "$$$$"
				//+ " M .PARDEPGROUP_CODE AS \"code\","
				+ " Q.PARQUOTA_CODE AS \"quotaCode\","
				+ " Q.QUOTA_DETAIL AS \"quotaValue\""
				+ " FROM"
				+ " DRGS.T_IndexCal M"
				+ " LEFT JOIN DRGS.T_QUOTADTL Q ON M .MR_ID = Q.PARMR_ID"
				+ " WHERE 1=1 ");
		
		if("all".equals(indexCal.getPardrgCode())){
			codeStr = " M.pardrg_code AS \"code\",";
			if("DRG_DHCC".equals(indexCal.getParadrgCode()))
			{
				sql.append(" and M.pardrg_code in (select tt.drg_code from drgs.t_drgs tt) ");
			}
			else
			{
				sql.append(" and M.pardrg_code in (select tt.drg_code from drgs.t_drgs tt where tt.adrg_code = '"+indexCal.getParadrgCode()+"')  ");
			}
	   	}else if (indexCal.getPardrgCode() != null && !"".equals(indexCal.getPardrgCode())&&("all".equals(indexCal.getPardepgroupCode())||"all".equals(indexCal.getPardepCode())||"all".equals(indexCal.getPargroupName()))) {
	   		sql.append(" and M.pardrg_code = '"+indexCal.getPardrgCode()+"'");
	   	}else{
	   		sql.append(" and M.pardrg_code = 'DRG_DHCC'");
	   	}
		
		if("all".equals(indexCal.getPargroupName())){
			codeStr = " M.pargroup_name AS \"code\",";
	   		sql.append(" and M.pargroup_name in  (select tt.group_name  from DRGS.T_CLINICGROUP tt where tt.pardep_code = '"+indexCal.getPardepCode()+"')");
	   	}else if (indexCal.getPargroupName() != null && !"".equals(indexCal.getPargroupName())&&("all".equals(indexCal.getParmdcCode())||"all".equals(indexCal.getParadrgCode())||"all".equals(indexCal.getPardrgCode()))) {
	   		sql.append(" and M.pargroup_name = '"+indexCal.getPargroupName()+"'");
	   	}else{
	   		sql.append(" and M.pargroup_name = 'DRG_DHCC'");
	   	}
	   	
		if("all".equals(indexCal.getParmdcCode())){
			codeStr = " M.parmdc_code AS \"code\",";
	   		sql.append(" and M.parmdc_code <> 'DRG_DHCC'");
	   	}else if (indexCal.getParmdcCode() != null && !"".equals(indexCal.getParmdcCode())&&("all".equals(indexCal.getPardepgroupCode())||"all".equals(indexCal.getPardepCode())||"all".equals(indexCal.getPargroupName()))) {
	   		sql.append(" and M.parmdc_code = '"+indexCal.getParmdcCode()+"'");
	   	}else{
	   		sql.append(" and M.parmdc_code = 'DRG_DHCC'");
	   	}
		
		if("all".equals(indexCal.getParadrgCode())){
			codeStr = " M.paradrg_code AS \"code\",";
	   		sql.append(" and M.paradrg_code in (select tt.adrg_code from drgs.T_ADRG tt where tt.mdc_code ='"+indexCal.getParmdcCode()+"')");
	   	}else if (indexCal.getParadrgCode() != null && !"".equals(indexCal.getParadrgCode())&&("all".equals(indexCal.getPardepgroupCode())||"all".equals(indexCal.getPardepCode())||"all".equals(indexCal.getPargroupName()))) {
	   		sql.append(" and M.paradrg_code = '"+indexCal.getParadrgCode()+"'");
	   	}else{
	   		sql.append(" and M.paradrg_code = 'DRG_DHCC'");
	   	}
		
		if("all".equals(indexCal.getProtomedicus())){
	   		sql.append(" and M.protomedicus <> 'DRG_DHCC'");
	   	}else if (indexCal.getProtomedicus() != null && !"".equals(indexCal.getProtomedicus())) {
	   		sql.append(" and M.protomedicus = '"+indexCal.getProtomedicus()+"'");
	   	}else{
	   		sql.append(" and M.protomedicus = 'DRG_DHCC'");
	   	}
		
		if("all".equals(indexCal.getResident())){
	   		sql.append(" and M.parresident_code <> 'DRG_DHCC'");
	   	}else if (StringUtils.hasLength(indexCal.getResident())) {
			sql.append(" and M.parresident_code = '"+indexCal.getResident()+"'");
		}else{
	   		sql.append(" and M.parresident_code = 'DRG_DHCC'");
		}
		
		if("all".equals(indexCal.getPardepCode())){
			codeStr = " M.pardep_code AS \"code\",";
	   		sql.append(" and M.pardep_code in (select tt.dep_code from drgs.T_DEPARTMENT tt where tt.pardepgroup_code = '"+indexCal.getPardepgroupCode()+"')");
	   	}else if (indexCal.getPardepCode() != null && !"all".equals(indexCal.getPardepCode())) {
	   		sql.append(" and M.pardep_code = '"+indexCal.getPardepCode()+"'");
	   	}else{
	   		sql.append(" and M.pardep_code = 'DRG_DHCC'");
	   	}
		
		if("all".equals(indexCal.getPardepgroupCode())){
			codeStr = " M.pardepgroup_code AS \"code\",";
	   		sql.append(" and M.pardepgroup_code <> 'DRG_DHCC'");
	   	}else if (indexCal.getPardepgroupCode() != null && !"".equals(indexCal.getPardepgroupCode())&&("all".equals(indexCal.getParmdcCode())||"all".equals(indexCal.getParadrgCode())||"all".equals(indexCal.getPardrgCode()))) {
	   		sql.append(" and M.pardepgroup_code = '"+indexCal.getPardepgroupCode()+"'");
	   	}else{
	   		sql.append(" and M.pardepgroup_code = 'DRG_DHCC'");
	   	}
		if(!"".equals(indexCal.getpType()) && null != indexCal.getpType()){
			sql.append(" and M.type = '"+indexCal.getpType()+"'");
		}else{
			sql.append(" and M.type = '0'");
		}
		// 注释报错
//   		if("month".equals(indexCal.getMrStdtype()) || "period".equals(indexCal.getMrStdtype())){
//   			sql.append(" and M.mrStdtype = 'period'");
//   		}else{
//   			sql.append(" and M.mr_Std_type = 'year'");
//   		}
   		sql.append(" AND M .MR_TYPE = 'M'");
   		if ("init".equals(indexCal.getType1()) && indexCal.getpType()!=null) {
			sql.append(" AND Q.PARQUOTA_CODE = '"+quotaCode+"' ");
		}
	   	sql.append("ORDER BY \"code\", \"month\"");
	   	String sqlstr = sql.toString().replace("$$$$", codeStr);
		List<Object[]> list = getQueryChannelService().createSqlQuery(sqlstr).list();
		
		return list;
	}
	
	/**
	 * 
	 * @methodname    :getQuatrantChart
	 * @description   : 科室指标象限分析
	 * @param         :
	 * @author        :fulianxia
	 * @return        :
	 * @date          :2015年5月13日
	 */
	@Override
	public List<Object[]> getDisQuatrantChart(IndexCal indexCal) {
		// 注释报错
//		String mrLot = indexCal.getMrLot();
		String mdcCode = indexCal.getParmdcCode();
		StringBuilder sql = new StringBuilder();
		sql.append("select dep.drg_code,dep.drg_desc,dtl.add_quota_code,"
		          + " case when dtl.add_quota_code='AvgFee' then round(dtl.add_value - age.manage_benchmark,2)  "
		          + " else round(dtl.add_value - age.manage_benchmark,2) end as value  "
		          + " from (select d.drg_code,d.drg_desc,mr.* from DRGs.t_archivedata mr "
		          + " left join DRGs.t_drgs d  on mr.ad_drg_code = d.drg_code "
		          + " where  mr.ad_depgroup_code ='DRG_DHCC' "
		          + " and mr.ad_mdc_code ='DRG_DHCC' "
		          + " and mr.ad_group_name ='DRG_DHCC' "
		          + " and mr.ad_doctor ='DRG_DHCC' "
		          + " and mr.ad_drg_code <>'DRG_DHCC' "
		          + " and mr.ad_adrg_code ='DRG_DHCC' "
		          + " and mr.ad_dep_code = 'DRG_DHCC' "
		          + " and d.ver_code='V2015'"
		          + " AND D.ADRG_CODE IN (SELECT ADRG.ADRG_CODE FROM DRGS.T_ADRG ADRG WHERE ADRG.MDC_CODE='"+mdcCode+"')"
		          + " ) dep "
		          + " left join DRGs.t_archivedatadtl dtl on dtl.add_id=dep.ad_id "
		          + " left join DRGs.t_quotamanage age on age.pardrg_code=dep.drg_code "
		       // 注释报错
//		          + " where dep.ad_lot='"+mrLot+"' and dep.ad_std=substr('"+mrLot+"',1,4) "
//		          + " and (dtl.add_quota_code='AvgFee' or dtl.add_quota_code='DrugRatio' or dtl.add_quota_code='AvgDays') and dtl.add_value <> 0 "
//		          + " and dtl.add_quota_code = age.parquota_code "
//		          + " and age.manage_year=substr('"+mrLot+"',1,4) and age.pardep_code is null "
		          + " and age.pardrg_code is not null  and age.pardepgroup_code is null ");
		List<Object[]> list = getQueryChannelService().createSqlQuery(sql.toString()).list();
		if(list.size() <= 0){
			sql = new StringBuilder();
			sql.append("  select dep.drg_code,dep.drg_desc,dtl.parquota_code,  "
			          + " case when dtl.parquota_code='AvgFee' then round(dtl.quota_detail - age.manage_benchmark,2)  "
			          + " else round(dtl.quota_detail - age.manage_benchmark,2) end as value   "
			          + " from (select d.drg_code,d.drg_desc,mr.* from DRGs.t_IndexCal mr "
			          + " left join DRGs.t_drgs d  on mr.pardrg_code = d.drg_code "
			          + " where  mr.pardepgroup_code ='DRG_DHCC' "
			          + " and mr.parmdc_code ='DRG_DHCC' "
			          + " and mr.pargroup_name ='DRG_DHCC' "
			          + " and mr.protomedicus ='DRG_DHCC' "
			          + " and mr.pardrg_code <>'DRG_DHCC' "
			          + " and mr.paradrg_code ='DRG_DHCC' "
			          + " and mr.pardep_code = 'DRG_DHCC' "
			          + " and mr.type=0 "
			          + " and d.ver_code='V2015'"
			          + " AND D.ADRG_CODE IN (SELECT ADRG.ADRG_CODE FROM DRGS.T_ADRG ADRG WHERE ADRG.MDC_CODE='"+mdcCode+"')"
			          + " ) dep "
			          + " left join DRGs.t_quotadtl dtl on dep.mr_id=dtl.parmr_id "
			          + " left join DRGs.t_quotamanage age on age.pardrg_code=dep.drg_code "
			       // 注释报错
//			          + " where dep.mr_lot='"+mrLot+"' and dep.mr_std_type='year' "
//			          + "  and (dtl.parquota_code='AvgFee' or dtl.parquota_code='DrugRatio' or dtl.parquota_code='AvgDays') and dtl.quota_detail <> 0 "
//			          + "  and dtl.parquota_code = age.parquota_code  "
//			          + "  and age.manage_year=substr('"+mrLot+"',1,4) and age.pardrg_code is not null  "
			          + "  and age.pardep_code is null   and age.pardepgroup_code is null "
			          + " order by dep.drg_code ");
			          
			list = getQueryChannelService().createSqlQuery(sql.toString()).list();
		}
		return list;
	}
	/**
	 * 
	 *  @Description：获取主院医师
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.IndexCalApplication#getResidentByDepCode(com.dhcc.DRGsHosp.core.domain.IndexCal)
	 */
	@Override
	public List<Map<String,Object>> getResidentByDepCode(IndexCal indexCal) {
		String dateFrom=DrgsIndex.formateDateFrom(indexCal.getDateFrom());
		String dateTo=DrgsIndex.formateDateTo(indexCal.getDateTo());	
		String hospCode=indexCal.getHospCode();
		String sqlStr=" select x.group_code CCODE, x.group_name DDESC,LISTAGG(x.dep_ward,'+')  WITHIN GROUP (ORDER BY x.group_code , x.group_name) DEP  \r\n" + 
				"from (\r\n" ; 
		if(indexCal.getParwardCode()!=null && !"".equals(indexCal.getParwardCode())){
			sqlStr+= "select distinct cg.group_code , cg.group_name, wa.ward_desc dep_ward \r\n" ; 
		}else {
			sqlStr+= "select distinct cg.group_code , cg.group_name, a.dep_name dep_ward \r\n" ; 
		}
		sqlStr+=" from drgs.mr_baseinfo mrb \n "+
				" left join  DRGs.MR_IPINFO t on mrb.pmainindex=t.pmainindex   \r\n" + 
				" left join DRGs.t_Department a on t.P00300001=a.dep_code \r\n" + 
				" left join drgs.t_ward wa on t.P00310001=wa.ward_code \r\n" + 
				" left join drgs.t_clinicgroup cg on t.P00600401=cg.group_code   \r\n" + 
				" where 1=1  and to_char(t.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"' \r\n" + 
				" and cg.group_code is not null   \r\n";
		if(StringUtils.hasLength(hospCode)){
			sqlStr+=" and instr('"+hospCode+"',mrb.p00010001)>0 \n ";
		}
		if(indexCal.getPardepgroupCode()!=null && !"".equals(indexCal.getPardepgroupCode())){
			sqlStr+=" and a.pardepgroup_code='"+indexCal.getPardepgroupCode()+"' \r\n ";
		}
		if(indexCal.getPardepCode()!=null && !"".equals(indexCal.getPardepCode())){
			sqlStr+=" and t.P00300001='"+indexCal.getPardepCode()+"' \r\n";
		}
		if(indexCal.getParwardCode()!=null && !"".equals(indexCal.getParwardCode())){
			sqlStr+=" and t.P00310001='"+indexCal.getParwardCode()+"' \r\n";
		}
		sqlStr+="	)x group by x.group_code , x.group_name";
		
		return jdbcTemplateObj.queryForList(sqlStr);
	 
	
	}
	
/**
 * 
 *  @Description：主治医师
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.IndexCalApplication#getPhysicianByDepCode(com.dhcc.DRGsHosp.core.domain.IndexCal)
 */
	@Override
	public List<Map<String,Object>> getPhysicianByDepCode(IndexCal indexCal) {
		String dateFrom=DrgsIndex.formateDateFrom(indexCal.getDateFrom());
		String dateTo=DrgsIndex.formateDateTo(indexCal.getDateTo());
		String hospCode=indexCal.getHospCode();
		String sqlStr=" select x.group_code CCODE, x.group_name DDESC,LISTAGG(x.dep_ward,'+')  WITHIN GROUP (ORDER BY x.group_code , x.group_name) DEP  \r\n" + 
				"from (\r\n" ; 
		if(indexCal.getParwardCode()!=null && !"".equals(indexCal.getParwardCode())){
			sqlStr+= "select distinct cg.group_code , cg.group_name, wa.ward_desc dep_ward \r\n" ; 
		}else {
			sqlStr+= "select distinct cg.group_code , cg.group_name, a.dep_name dep_ward \r\n" ; 
		}
		sqlStr+=" from drgs.mr_baseinfo mrb \n "+
				" left join  DRGs.MR_IPINFO t on mrb.pmainindex=t.pmainindex   \r\n" + 
				" left join DRGs.t_Department a on t.P00300001=a.dep_code \r\n" + 
				" left join drgs.t_ward wa on t.P00310001=wa.ward_code \r\n" + 
				" left join drgs.t_clinicgroup cg on t.P00600301=cg.group_code   \r\n" + 
				" where 1=1  and to_char(t.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"' \r\n" + 
				" and cg.group_code is not null  \r\n";
		if(StringUtils.hasLength(hospCode)){
			sqlStr+=" and instr('"+hospCode+"',mrb.p00010001)>0 \n ";
		}
		if(indexCal.getPardepgroupCode()!=null && !"".equals(indexCal.getPardepgroupCode())){
			sqlStr+=" and a.pardepgroup_code='"+indexCal.getPardepgroupCode()+"' \r\n ";
		}
		if(indexCal.getPardepCode()!=null && !"".equals(indexCal.getPardepCode())){
			sqlStr+=" and t.P00300001='"+indexCal.getPardepCode()+"' \r\n";
		}
		if(indexCal.getParwardCode()!=null && !"".equals(indexCal.getParwardCode())){
			sqlStr+=" and t.P00310001='"+indexCal.getParwardCode()+"' \r\n";
		}
		sqlStr+="	)x group by x.group_code , x.group_name";
		
		return jdbcTemplateObj.queryForList(sqlStr);
	}
	/**
	 * 
	 *  @Description：按科室代码查询副主任医师
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.IndexCalApplication#getProtomedicusByDepCode(com.dhcc.DRGsHosp.core.domain.IndexCal)
	 */
	@Override
	public List<Map<String,Object>> getProtomedicusByDepCode(IndexCal indexCal){
		String dateFrom=DrgsIndex.formateDateFrom(indexCal.getDateFrom());
		String dateTo=DrgsIndex.formateDateTo(indexCal.getDateTo());
		String hospCode=indexCal.getHospCode();
		String sqlStr=" select x.group_code CCODE, x.group_name DDESC,LISTAGG(x.dep_ward,'+')  WITHIN GROUP (ORDER BY x.group_code , x.group_name) DEP  \r\n" + 
				"from ( \r\n" ; 
		if(indexCal.getParwardCode()!=null && !"".equals(indexCal.getParwardCode())){
			sqlStr+= "select distinct cg.group_code , cg.group_name, wa.ward_desc dep_ward \r\n" ; 
		}else {
			sqlStr+= "select distinct cg.group_code , cg.group_name, a.dep_name dep_ward \r\n" ; 
		}
		sqlStr+="  from drgs.mr_baseinfo mrb \n "+
				"  left join  DRGs.MR_IPINFO t on mrb.pmainindex=t.pmainindex   \r\n" + 
				"  left join DRGs.t_Department a on t.P00300001=a.dep_code \r\n" + 
				"  left join drgs.t_ward wa on t.P00310001=wa.ward_code \r\n" + 
				" left join drgs.t_clinicgroup cg on t.P00600201=cg.group_code   \r\n" + 
				" where 1=1  and to_char(t.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"' \r\n" + 
				" and cg.group_code is not null   \r\n";
		if(StringUtils.hasLength(hospCode)){
			sqlStr+=" and instr('"+hospCode+"',mrb.p00010001)>0 \n ";
		}
		if(indexCal.getPardepgroupCode()!=null && !"".equals(indexCal.getPardepgroupCode())){
			sqlStr+=" and a.pardepgroup_code='"+indexCal.getPardepgroupCode()+"' \r\n ";
		}
		if(indexCal.getPardepCode()!=null && !"".equals(indexCal.getPardepCode())){
			sqlStr+=" and t.P00300001='"+indexCal.getPardepCode()+"' \r\n";
		}
		if(indexCal.getParwardCode()!=null && !"".equals(indexCal.getParwardCode())){
			sqlStr+=" and t.P00310001='"+indexCal.getParwardCode()+"' \r\n";
		}
		sqlStr+="	)x group by x.group_code , x.group_name";
		
		return jdbcTemplateObj.queryForList(sqlStr);
	
	}
	
	/**
	 * 
	 *  @Description：获取主诊组
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.IndexCalApplication#getClinicByDepCode(com.dhcc.DRGsHosp.core.domain.IndexCal)
	 */
	@Override
	public List<Map<String,Object>> getClinicByDepCode(IndexCal indexCal){
		String dateFrom=DrgsIndex.formateDateFrom(indexCal.getDateFrom());
		String dateTo=DrgsIndex.formateDateTo(indexCal.getDateTo());
		String hospCode=indexCal.getHospCode();
		String sqlStr=" select x.group_code CCODE, x.group_name DDESC,LISTAGG(x.dep_ward,'+')  WITHIN GROUP (ORDER BY x.group_code , x.group_name) DEP  \r\n" + 
				"from (\r\n" ; 
		if(indexCal.getParwardCode()!=null && !"".equals(indexCal.getParwardCode())){
			sqlStr+= "select distinct cg.group_code , cg.group_name, wa.ward_desc dep_ward \r\n" ; 
		}else {
			sqlStr+= "select distinct cg.group_code , cg.group_name, a.dep_name dep_ward \r\n" ; 
		}
		sqlStr+=" from drgs.mr_baseinfo mrb \n "+
				" left join  DRGs.MR_IPINFO t on mrb.pmainindex=t.pmainindex   \r\n" + 
				" left join DRGs.t_Department a on t.P00300001=a.dep_code \r\n" + 
				" left join drgs.t_ward wa on t.P00310001=wa.ward_code \r\n" + 
				" left join drgs.t_clinicgroup cg on t.P00601001=cg.group_code   \r\n" + 
				" where 1=1  and to_char(t.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"' \r\n" + 
				" and cg.group_code is not null  \r\n";
		if(StringUtils.hasLength(hospCode)){
			sqlStr+=" and instr('"+hospCode+"',mrb.p00010001)>0 \n ";
		}
		if(indexCal.getPardepgroupCode()!=null && !"".equals(indexCal.getPardepgroupCode())){
			sqlStr+=" and a.pardepgroup_code='"+indexCal.getPardepgroupCode()+"' \r\n ";
		}
		if(indexCal.getPardepCode()!=null && !"".equals(indexCal.getPardepCode())){
			sqlStr+=" and t.P00300001='"+indexCal.getPardepCode()+"' \r\n";
		}
		if(indexCal.getParwardCode()!=null && !"".equals(indexCal.getParwardCode())){
			sqlStr+=" and t.P00310001='"+indexCal.getParwardCode()+"' \r\n";
		}
		sqlStr+="	)x group by x.group_code , x.group_name";
		
		return jdbcTemplateObj.queryForList(sqlStr);
	
	}
	/**
	 * 
	 *  @Description：获取科室下病区
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.MonitorResultApplication#findWardsByCondition(com.dhcc.DRGsHosp.core.domain.IndexCal)
	 */
	@Override
	public List<Map<String, Object>> findWardsByCondition(IndexCal indexCal) {
		String dateFrom=DrgsIndex.formateDateFrom(indexCal.getDateFrom());
		String dateTo=DrgsIndex.formateDateTo(indexCal.getDateTo());
		String hospCode=indexCal.getHospCode();
		String sql="select distinct(a.ward_code) ,a.ward_desc  from DRGs.MR_IPINFO t \n" + 
				  " left join drgs.mr_baseinfo mrb on t.pmainindex=mrb.pmainindex \n"
				+ " left join DRGs.t_ward a on t.P00310001=a.ward_code \n" + 
				" WHERE to_char(t.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"' \n" + 
				" AND a.ward_code  IS NOT NULL \n" ;
		if(indexCal.getPardepCode()!=null && !"".equals(indexCal.getPardepCode())){
			sql +=" and t.P00300001='"+indexCal.getPardepCode()+"' \n ";
		}
		if(StringUtils.hasLength(hospCode)){
			sql+=" and instr('"+hospCode+"',mrb.p00010001)>0 \n ";
		}
		return jdbcTemplateObj.queryForList(sql.toString());
	}

}
